﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace DBUtility
{
    public  class SQLHelper
    {

        public static readonly string ConnectionStr = ConfigurationSettings.AppSettings["ConnectionString"].ToString();
        //public static SqlConnection [] MyConns = { new SqlConnection(ConnectionStr), new SqlConnection(ConnectionStr), new SqlConnection(ConnectionStr), new SqlConnection(ConnectionStr), new SqlConnection(ConnectionStr) };

        /// <summary>
        /// 从数据库链接数组中获取空闲链接
        /// </summary>
        /// <returns></returns>
         SqlConnection GetSqlConnection()
        {
                SqlConnection MyConn = new SqlConnection(ConnectionStr);
                MyConn.Open();
                return MyConn;
        }

        /// <summary>
        /// 执行sql语句返回真假
        /// </summary>
        /// <param name="sqlStr">要执行的sql语句</param>
        /// <returns></returns>
        public  bool SqlExec(String sqlStr)
        {
            SqlConnection MyConn = GetSqlConnection();
            SqlCommand cmd = new SqlCommand(sqlStr, MyConn);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException e)
            {
                throw new ArgumentException(e.ToString());
                return false;
            }
            finally
            {
                cmd.Dispose();
                MyConnClose(MyConn);
            }
        }
        /// <summary>
        /// 通过SQL语句查询获取DATATABLE数据
        /// </summary>
        /// <param name="SelectSql"></param>
        /// <returns></returns>
        public  DataTable GetDataTableBySqlStr(String SelectSql)
        {
            SqlConnection MyConn = GetSqlConnection();
            SqlCommand cmd = new SqlCommand(SelectSql, MyConn);
            SqlDataAdapter MyDataAdapter = new SqlDataAdapter();
            try
            {
                MyDataAdapter.SelectCommand = cmd;
                DataTable dt = new DataTable();
                MyDataAdapter.Fill(dt);
                return dt;
            }
            catch (SqlException e)
            {
                throw new ArgumentException(e.ToString());
                return null;
            }
            finally
            {
                MyDataAdapter.Dispose();
                cmd.Dispose();
                MyConnClose(MyConn);
            }
        }
        /// <summary>
        /// 用于查询返回记录的总数
        /// </summary>
        /// <param name="SelectSql"></param>
        /// <returns></returns>
        public  int GetCountBySqlStr(String SelectSql)
        {
            SqlConnection MyConn = GetSqlConnection();
            SqlCommand cmd = new SqlCommand(SelectSql, MyConn);
            try
            {
                return (int)cmd.ExecuteScalar();
            }
            catch (SqlException e)
            {
                throw new ArgumentException(e.ToString());
                return 0;
            }
            finally
            {
                cmd.Dispose();
                MyConnClose(MyConn);
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="ProcText">存储过程名</param>
        /// <param name="PrcParameters">参数</param>
        /// <returns></returns>
        public  bool ProcExcut(String ProcText, SqlParameter[] PrcParameters)
        {
            SqlConnection MyConn = GetSqlConnection();
            SqlCommand cmd = new SqlCommand(ProcText, MyConn);
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                AttachParameters(cmd, PrcParameters);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException e)
            {
                throw new ArgumentException(e.ToString());
                return false;
            }
            finally
            {
                cmd.Dispose();
                MyConnClose(MyConn);
            }
        }
        /// <summary>
        /// 执行存储过程返回数据集
        /// </summary>
        /// <param name="ProcText">存储过程名</param>
        /// <param name="PrcParameters">参数</param>
        /// <returns></returns>
        public  DataTable GetDatatableProcExcut(String ProcText, SqlParameter[] PrcParameters)
        {
            SqlConnection MyConn = GetSqlConnection();
            SqlCommand cmd = new SqlCommand(ProcText, MyConn);
            SqlDataAdapter MyDataAdapter = new SqlDataAdapter();
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                AttachParameters(cmd, PrcParameters);
                MyDataAdapter.SelectCommand = cmd;
                DataTable dt = new DataTable();
                MyDataAdapter.Fill(dt);
                MyDataAdapter.Dispose();
                cmd.Parameters.Clear();
                return dt;
            }
            catch (SqlException e)
            {
                throw new ArgumentException(e.ToString());
                return null;
            }
            finally
            {
                cmd.Dispose();
                MyConnClose(MyConn);
            }
        }
        /// <summary>
        /// 获取分页查询单页信息
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="where"></param>
        /// <param name="Clums"></param>
        /// <param name="idClums"></param>
        /// <param name="rows"></param>
        /// <param name="page"></param>
        /// <returns></returns>
        public DataTable GetPageListByProc(String TableName,String where,String Clums,String idClums,int rows,int page)
        {
                SqlParameter[] PrcParameters = new SqlParameter[]{
                new SqlParameter("@TABLENAME",TableName),
                new SqlParameter("@WHERE",where),
                new SqlParameter("@CLUMS",Clums),
                new SqlParameter("@IDCLUMS",idClums),
                new SqlParameter("@ROWS",rows),
                new SqlParameter("@PAGE",page),
                new SqlParameter("@ISCOUNT","0")};
                return GetDatatableProcExcut("PRC_PAGE", PrcParameters);
        }
        /// <summary>
        /// 获取分页查询记录总数
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public String GetPageListByProc(String TableName, String where)
        {
            SqlParameter[] PrcParameters = new SqlParameter[]{
                new SqlParameter("@TABLENAME",TableName),
                new SqlParameter("@WHERE",where),
                new SqlParameter("@CLUMS",""),
                new SqlParameter("@IDCLUMS",""),
                new SqlParameter("@ROWS",""),
                new SqlParameter("@PAGE",""),
                new SqlParameter("@ISCOUNT","1")};
            return GetDatatableProcExcut("PRC_PAGE", PrcParameters).Rows[0][0].ToString();
        }
        /// <summary>
        /// 将参数绑定到cmd
        /// </summary>
        /// <param name="command">传入的sqlcmd</param>
        /// <param name="commandParameters">传入的参数集合</param>
        /// <returns></returns>
        private   void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (SqlParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }
        /// <summary>
        /// 关闭数据库链接
        /// </summary>
        /// <param name="conn"></param>
        void  MyConnClose(SqlConnection conn)
        {
            if (conn.State == ConnectionState.Open)
                conn.Close();
            conn.Dispose();
        }
    }
}
